#!/usr/bin/env python
# -*- coding: utf-8 -*-

from django.db import connection

from web.dao.base_dao import BaseDao
from web.domain.maximum_drawdown import MaximumDrawDown
from web.manager.log_manager import LogManager
from web.models.optimize2_account_log import Optimize2AccountLog
from web.models.quant2_account_log import Quant2AccountLog

Logger = LogManager.get_logger(__name__)

class Optimize2AccountLogDao(BaseDao):
    """
    Optimize2AccountLog的dao类
    """

    model_class = Optimize2AccountLog

    def insert(self, date: str, account_name: str):
        """
        插入记录
        """

        with connection.cursor() as cursor:
            sql: str = "insert into optimize2_account_log(DATE_, ACCOUNT_NAME, HOLD_COMMODITY_FUTURE_NUMBER, COMMODITY_FUTURE_ASSETS, " \
                       "CAPITAL_ASSETS, TOTAL_ASSETS, TOTAL_OPEN_COMMISSION, TOTAL_CLOSE_COMMISSION) " \
                       "select to_date({}, 'yyyy-mm-dd'), t.ACCOUNT_NAME, t.HOLD_COMMODITY_FUTURE_NUMBER, t.COMMODITY_FUTURE_ASSETS, t.CAPITAL_ASSETS, t.TOTAL_ASSETS, " \
                       "TOTAL_OPEN_COMMISSION, TOTAL_CLOSE_COMMISSION " \
                       "from optimize2_account t where t.account_name = '{}'".format(date, account_name)

            cursor.execute(sql)

    def write_ma_by_date(self, date):
        """
        根据日期，计算ma数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_OPTIMIZE2_ACCOUNT_LOG.WRITE_MOVING_AVERAGE_BY_DATE", [date])

    def write_bias_by_date(self, date):
        """
        根据日期，计算bias数据
        """

        with connection.cursor() as cursor:
            cursor.callproc("PKG_OPTIMIZE2_ACCOUNT_LOG.WRITE_BIAS_BY_DATE", [date])

    def calculate_maximum_drawdown(self) -> list[MaximumDrawDown]:
        """
        计算最大回撤
        """

        with connection.cursor() as cursor:
            sql: str = "select * from (" \
                "select (t1.total_assets-t2.total_assets)/t1.total_assets*100 MAXIMUM_DRAWDOWN, " \
                "t1.date_ DATE1, t2.date_ DATE2, t1.account_name ACCOUNT_NAME, t1.total_assets TOTAL_ASSETS1, " \
                "t2.total_assets TOTAL_ASSETS2 " \
                "from optimize2_account_log t1, optimize2_account_log t2 " \
                "where t1.account_name=t2.account_name and t1.date_<t2.date_ " \
                "order by (t1.total_assets-t2.total_assets)/t1.total_assets*100 desc) " \
                "where rownum<=10"
            cursor.execute(sql)
            tuple_list: list[MaximumDrawDown] = cursor.fetchall()
            if tuple_list is not None and len(tuple_list) > 0:
                maximum_drawdown_list: list[MaximumDrawDown] = list()
                for _tuple in tuple_list:
                    maximum_drawdown: MaximumDrawDown = MaximumDrawDown(_tuple[0], _tuple[1], _tuple[2], _tuple[3], _tuple[4], _tuple[5])
                    maximum_drawdown_list.append(maximum_drawdown)
                return maximum_drawdown_list
            else:
                return None

    def find_by_begin_date_and_end_date_order_by_date_and_account_name(self, begin_date: str, end_date: str) -> list[Optimize2AccountLog]:
        """
        根据开始时间、结束时间查询记录，并按照date_和account_name列升序排列
        """

        with connection.cursor() as cursor:
            sql: str = "select * from optimize2_account_log t " \
                       "where t.date_ between to_date({},'yyyy-mm-dd') and to_date({},'yyyy-mm-dd') " \
                       "order by t.date_ asc, t.account_name asc".format(begin_date, end_date)
            cursor.execute(sql)
            tuple_list: list[Optimize2AccountLog] = cursor.fetchall()
            return self.tuple_list_to_optimize2_account_log_list(tuple_list)

    def find_by_account_name_order_by_date_desc(self, account_name: str) -> list[Optimize2AccountLog]:
        """
        根据账号查询记录，并按照日期降序排列
        """

        with connection.cursor() as cursor:
            sql: str = "select * from optimize2_account_log t where t.account_name = '{}' order by t.date_ desc".format(account_name)
            cursor.execute(sql)
            tuple_list = cursor.fetchall()
            return self.tuple_list_to_optimize2_account_log_list(tuple_list)

    def tuple_list_to_optimize2_account_log_list(self, tuple_list: list) -> list[Optimize2AccountLog]:
        """
        将tuple列表转换为Optimize2AccountLog列表
        """

        if tuple_list is not None and len(tuple_list) > 0:
            optimize2_account_log_list: list[Optimize2AccountLog] = list()
            for _tuple in tuple_list:
                optimize2_account_log: Optimize2AccountLog = Optimize2AccountLog()
                optimize2_account_log.id = _tuple[0]
                optimize2_account_log.date_ = _tuple[1]
                optimize2_account_log.account_name = _tuple[2]
                optimize2_account_log.hold_commodity_future_number = _tuple[3]
                optimize2_account_log.commodity_future_assets = _tuple[4]
                optimize2_account_log.capital_assets = _tuple[5]
                optimize2_account_log.total_assets = _tuple[6]
                optimize2_account_log.total_open_commission = _tuple[7]
                optimize2_account_log.total_close_commission = _tuple[8]
                optimize2_account_log.ma5 = _tuple[9]
                optimize2_account_log.ma10 = _tuple[10]
                optimize2_account_log.ma20 = _tuple[11]
                optimize2_account_log.ma60 = _tuple[12]
                optimize2_account_log.ma120 = _tuple[13]
                optimize2_account_log.ma250 = _tuple[14]
                optimize2_account_log.bias5 = _tuple[15]
                optimize2_account_log.bias10 = _tuple[16]
                optimize2_account_log.bias20 = _tuple[17]
                optimize2_account_log.bias60 = _tuple[18]
                optimize2_account_log.bias120 = _tuple[19]
                optimize2_account_log.bias250 = _tuple[20]
                optimize2_account_log_list.append(optimize2_account_log)
            return optimize2_account_log_list
        else:
            return None